package Apogeesrc;


/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author Ed
 */
//imports

import java.sql.*;
import java.util.*;
import java.io.*;
import java.net.*;
import javax.swing.JOptionPane;

public class sqlConnect extends javax.swing.JFrame{
    private Connection con;
    private String connectionUrl;
    private  String uName;
    private  String pWord;
    private Statement stmt;
    private ResultSet rs;
   public static String sysUser;
    private String hostName;
   private String fileName="";
   private FileInputStream fis;
    private Properties p;
    //private static String setUser;

    public sqlConnect()
    {
        
        p = new Properties();

       
       
    }
    /*private void openStream()
    {
        try
        {
        fis=new FileInputStream(fileName);
        p.load(fis);
        }
        catch(Exception ex)
        {
            JOptionPane.showMessageDialog(null,ex,"Error",JOptionPane.ERROR_MESSAGE);
        }
    }*/
    public boolean openConnection()
    {
         //connect to database

        boolean ok=true;
        //openStream();
        //uName=p.getProperty("username");
       // pWord=p.getProperty("password");
        try
        {
             
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
/*           if(sysUser.equals("Administrator"))
           {
              hostName="localhost";
           }
           else
           {
              hostName=getHostName();
           }
           
*/
            connectionUrl= "jdbc:odbc:apogee";

           con = DriverManager.getConnection(connectionUrl,"root","chrisujt782");

           stmt = con.createStatement();
        }
        catch (SQLException e)
        {
            JOptionPane.showMessageDialog(null, e+"\n Please Check your Connection Settings","Error",JOptionPane.ERROR_MESSAGE);
            ok=false;
        }
        catch (ClassNotFoundException cE)
        {
            System.out.println("Class Not Found Exception: "+ cE.toString());
            ok=false;
        }
        return ok;
    }
    public ResultSet select(String query)
    {
        if(openConnection()==true)
        {
        try
        {
            rs = stmt.executeQuery(query);
            return rs;
        }
        catch(Exception ex)
        {
            System.out.println(ex);
        }
        closeConnection();
      
        return rs;
        }
        return null;
    }
   
    public void add(String query)
    {
        
        openConnection();
        try
        {
        stmt.execute(query);
        }
        catch(Exception ex)
        {
            System.out.println(ex+" Add");
        }
        closeConnection();
    }
    public void update(String query)
    {
        openConnection();
        try
        {
            stmt.execute(query);
        }
        catch(Exception ex)
        {
            System.out.println(ex);
        }
        closeConnection();
    }
    public void fillCBox(javax.swing.JComboBox cBox,String colName, String tableName, String orderBy)
    {
        openConnection();
        String query="Select "+colName+" from "+tableName+" where status like 'active' order by "+orderBy;
        try
        {
             rs = stmt.executeQuery(query);
         while (rs.next()) {
                cBox.addItem(rs.getString(colName));
            }
        }
        catch(Exception ex)
        {
            System.out.println(ex);
        }
        closeConnection();
    }
    public void fillCBox2(javax.swing.JComboBox cBox,String colName, String tableName, String orderBy)
    {
        openConnection();
        String query="Select "+colName+" from "+tableName+" order by "+orderBy;
        try
        {
             rs = stmt.executeQuery(query);
         while (rs.next()) {
                cBox.addItem(rs.getString(colName));
            }
        }
        catch(Exception ex)
        {
            System.out.println(ex);
        }
        closeConnection();
    }
    public String getID(String likeStr, String selCol,String whereCol, String tableName)
    {
        String temp="-1";
        openConnection();
        
         String query="Select "+selCol+" from "+tableName+" where "+whereCol+" like '"+likeStr+"'";
         try
        {
             rs = stmt.executeQuery(query);
         while (rs.next()) {
                temp= rs.getString(selCol);
            }
        }
        catch(Exception ex)
        {
            System.out.println(ex);
            closeConnection();
            return temp;
        }
        closeConnection();

        return temp;
        
    }
/*    public Products getProduct(int id)
    {
        Products pr=new Products();
        openConnection();
        String query="Select Product_Name, Category_Num, Price, Reorder_Level, In_Stock, Status from Products where Product_Num like '"+id+"'";
        try
        {
             rs = stmt.executeQuery(query);
         while (rs.next()) {
                pr.setProductName(rs.getString("Product_Name"));
                pr.setInStock(rs.getString("In_Stock"));
                pr.setCategory(rs.getString("Category_Num"));
                pr.setReorderLevel(rs.getString("Reorder_Level"));
                pr.setStatus(rs.getString("Status"));
                pr.setPrice(rs.getString("Price"));
            }
        }
        catch(Exception ex)
        {
            System.out.println(ex);
            closeConnection();
            return null;
        }

        closeConnection();
        return pr;
 *///   }
    public void getCategory(int id,Object[] obj)
    {
        
        openConnection();
       
        String query="Select Category_Name, Status from Category where Category_Num like '"+id+"'";
        try
        {
             rs = stmt.executeQuery(query);
             
             while (rs.next())
             {
               obj[0]=rs.getString("Category_Name");
               obj[1]=rs.getString("Status");
               System.out.println(obj[0]);

             }
             
             
        }
        catch(Exception ex)
        {
            System.out.println(ex);
            closeConnection();

        }

        closeConnection();
    }
    public void closeConnection()
    {
        try
        {

        stmt.close();
        con.close();
        //rs.close();

        }
        catch(Exception ex)
        {

             System.out.println(ex);
        }
    }
   public void setSysUser(String userName)
   {
       sysUser=userName;
   }
   public String getHostName()
   {
       String host="";
       try
       {
           host=p.getProperty("hostname");
       }
       catch(Exception ex)
       {
           System.out.println(ex);
       }
       return host;
   }
   
   public void setConnectionSettings(String hostname, String username, String password)
   {
       
        try
        {
            p.put("hostname", hostname);
            p.put("username", username);
            p.put("password", password);
            FileOutputStream out = new FileOutputStream(fileName);
            p.save(out, "/* properties updated */");
        }
        catch(Exception ex)
        {
            System.out.println(ex);
        }
   }


}
